﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OPTFleet.OPTFleet;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace OPTFleet.Data
{
    class GestaoClientes
    {
        private static String tabelaClientes = "clientes";
        private static String[] chavesClientes = { "idcliente" };
        private static String[] camposClientes = { "nome", "prioridade", "idlocal", "abertura", "fecho" };

        private Conexao sqlConnection;

        public Conexao SqlConnection
        {
            set { sqlConnection = value; }
        }

        public GestaoClientes()
        {
            this.sqlConnection = new Conexao();
            this.sqlConnection.connect();
        }

        public Local GetLocalCliente(int idlocal)
        {
            Local local=null;
            String stmtSql;
            SqlDataReader rs;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "SELECT idlocal,morada1,morada2,cidade,regiao,codpostal,geocode.Long AS Long,geocode.Lat as Lat FROM locais WHERE idlocal = @IDLocal";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@IDLocal", SqlDbType.Int);
            sqlCmd.Parameters["@IDLocal"].Value = idlocal;

            rs = sqlCmd.ExecuteReader();

            local = MapLocal(rs);
            local.IDLocal = idlocal;

            rs.Close();

            return local;
        }

        public Cliente ConsultarCliente(int idCliente)
        {
            Cliente cliente;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String[] parameters = new String[15];
            String stmtSql;
            SqlDataReader rs;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            parameters[0] = tabelaClientes + "." + chavesClientes[0];
            parameters[1] = tabelaClientes + "." + camposClientes[0];
            parameters[2] = tabelaClientes + "." + camposClientes[1];
            parameters[3] = tabelaClientes + "." + camposClientes[2];
            parameters[4] = tabelaClientes + "." + camposClientes[3];
            parameters[5] = tabelaClientes + "." + camposClientes[4];
            parameters[6] = tabelaClientes;
            parameters[7] = tabelaClientes + "." + chavesClientes[0];

            /*
            SELECT idcliente,nome,prioridade,idlocal,abertura,fecho 
            FROM clientes
            WHERE (clientes.idcliente=idcliente)
            */

            stmtSql = string.Format("SELECT {0},{1},{2},{3},{4},{5} FROM {6} WHERE ({7} = @IDCliente)", parameters);
            
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@IDCliente", SqlDbType.Int);
            sqlCmd.Parameters["@IDCliente"].Value = idCliente;

            rs = sqlCmd.ExecuteReader();

            cliente = MapCliente(rs);

            rs.Close();

            return cliente;
        }

        private Cliente MapCliente(DbDataReader rs)
        {
            Cliente c = null;
            SqlDataReader sdr = (SqlDataReader)rs;
            int idlocal;

            if (sdr.Read())
            {
                c = new Cliente();
                c.IDcliente = (int)sdr[chavesClientes[0]];
                c.Nome = ((String)sdr[camposClientes[0]]).Trim();
                c.Prioridade = (int)sdr[camposClientes[1]];
                c.Aberturajanela = (int)sdr[camposClientes[3]];
                c.Fechojanela = (int)sdr[camposClientes[4]];

                idlocal = (int)rs["idlocal"];
                sdr.Close();
                c.Local = GetLocalCliente(idlocal);
            }
            else sdr.Close();

            return c;
        }

        private Local MapLocal(DbDataReader rs)
        {
            Local l = null;
            SqlDataReader sdr = (SqlDataReader)rs;

            if (sdr.Read())
            {
                l = new Local();
                l.Morada1 = ((string)sdr["morada1"]).Trim();
                l.Morada2 = ((string)sdr["morada2"]).Trim();
                l.Cidade = ((string)sdr["cidade"]).Trim();
                l.Regiao = ((string)sdr["regiao"]).Trim();
                l.Codpostal = ((string)sdr["codpostal"]).Trim();
                l.Longitude = (Double)sdr["Long"];
                l.Latitude = (Double)sdr["Lat"];
                sdr.Close();
            }
            else sdr.Close();

            return l;
        }

        public SortedDictionary<Int32,String> GetListClientes()
        {
            SortedDictionary<Int32, String> sd = new SortedDictionary<Int32, String>();
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            SqlDataReader rs;
            Int32 idc;
            String nome;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = string.Format("SELECT idcliente,nome FROM clientes");
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            rs = sqlCmd.ExecuteReader();

            while (rs.Read())
            {
                idc = (Int32)rs["idcliente"];
                nome = ((String)rs["nome"]).Trim();
                sd.Add(idc, nome);
            }

            rs.Close();
            return sd;
        }
    }
}
